package example.dao;

import example.model.User;
import example.utils.DBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.util.List;

public class UserDao {
    private final QueryRunner queryRunner = new QueryRunner(DBUtils.getDataSource());

    public int addUser(String name, String email) throws Exception {
        String sql = "INSERT INTO user (username, password) VALUES (?, ?)";
        return queryRunner.update(sql, name, email);
    }

    public User getUserById(int id) throws Exception {
        String sql = "SELECT * FROM user WHERE id = ?";
        return queryRunner.query(sql, new BeanHandler<>(User.class), id);
    }

    public List<User> getAllUser() throws Exception {
        String sql = "SELECT * FROM user";
        return queryRunner.query(sql, new BeanListHandler<>(User.class));
    }

    public int updateUser(int id, String name, String email) throws Exception {
        String sql = "UPDATE user SET username = ?, password = ? WHERE id = ?";
        return queryRunner.update(sql, name, email, id);
    }

    public int deleteUser(int id) throws Exception {
        String sql = "DELETE FROM user WHERE id = ?";
        return queryRunner.update(sql, id);
    }

    public static void main(String[] args) throws Exception {
        UserDao userDao = new UserDao();
        System.out.println(userDao.getUserById(1).getUsername());
    }

    public User getUserByUserNameAndPassword(String username, String password) {
        String sql = "SELECT * FROM user WHERE username = ? AND password = ?";
        try {
            return queryRunner.query(sql, new BeanHandler<>(User.class), username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<User> getUserList(String sql, int offset, int pageSize) {
        try {
            return queryRunner.query(sql, new BeanListHandler<>(User.class), offset, pageSize);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public int getTotalCount(String sql) {
        try {
            return queryRunner.query(sql, new ScalarHandler<Long>()).intValue();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }
}
